package com.anjie.powerproject.repository;

import com.anjie.powerproject.entity.WeatherData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface WeatherRepository extends JpaRepository<WeatherData, Long> {
    @Query(nativeQuery = true, value = "select (@i \\:= @i + 1) as id, DATE_FORMAT(sdate, '%Y-%m-%d') as sdate, temphigh as day_t_max, templow as day_t_min, wind as day_wind, " +
            "humidity_high as day_hum_max, humidity_low as day_hum_min, weather as day_weather from (select @i \\:= 0) r, weather_forecast_daily " +
            "where historycity_code = ?1 and sdate >= ?2 and sdate <= ?3")
    List<WeatherData> getDayWeatherListByDate(String cityCode, String startTime, String endTime);

    @Query(nativeQuery = true, value = "select (@i \\:= @i - 1) as id, DATE_FORMAT(sdate, '%Y-%m-%d') as sdate, cast(temphigh as decimal(9, 2)) as day_t_max, cast(templow as decimal(9, 2)) as day_t_min, cast(wind as decimal(9, 2)) as day_wind, " +
            "cast(humidity_high as decimal(9, 2)) as day_hum_max, cast(humidity_low as decimal(9, 2)) as day_hum_min, weather as day_weather from (select @i \\:= 0) r, weather_history_daily " +
            "where historycity_code = ?1 and sdate >= ?2 and sdate <= ?3")
    List<WeatherData> getHistoryDayWeatherListByDate(String cityCode, String startTime, String endTime);

    @Query(nativeQuery = true, value = "select (@i \\:= @i - 1) as id, DATE_FORMAT(sdate, '%Y-%m-%d') as sdate, cast(temphigh as decimal(9, 2)) as day_t_max, cast(templow as decimal(9, 2)) as day_t_min, cast(wind as decimal(9, 2)) as day_wind, " +
            "cast(humidity_high as decimal(9, 2)) as day_hum_max, cast(humidity_low as decimal(9, 2)) as day_hum_min, weather as day_weather from (select @i \\:= 0) r, weather_forecast_daily " +
            "where historycity_code = ?1 and  sdate = ?2")
    WeatherData getWeatherForecastDataByDate(String cityCode,String time);

    @Query(nativeQuery = true, value = "select (@i \\:= @i - 1) as id, DATE_FORMAT(sdate, '%Y-%m-%d %H:%i:%s') as sdate, cast(temperature as decimal(9, 2)) as day_t_max, cast(temperature as decimal(9, 2)) as day_t_min, cast(wind as decimal(9, 2)) as day_wind, " +
            "cast(humidity as decimal(9, 2)) as day_hum_max, cast(humidity as decimal(9, 2)) as day_hum_min, weather as day_weather from (select @i \\:= 0) r, weather_forecast_hourly " +
            "where historycity_code = ?1 and  sdate = ?2")
    WeatherData getWeatherForecastDataByHour(String cityCode,String time);
}
